package mvc.core.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import mvc.core.BeanFactory;
import mvc.core.log.Logger;
import mvc.core.util.Util;

public class DB {
	
	private static Logger logger = Logger.getLogger(DB.class);
	
	private static DataSource ds = BeanFactory.getBean(DataSource.class);
	
	public static Connection connect() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			return DriverManager.getConnection(ds.getUrl(), ds.getUsername(), ds.getPassword());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public static PageData executeQuery(String sql, List<Object> params) {
		PageData pageData = new PageData();
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			conn = DB.connect();
			stmt = conn.prepareStatement(sql);
			addParams(sql, stmt, params);
			List<Map<String, Object>> table = new ArrayList<>();
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				Map<String, Object> row = new HashMap<>();
				for (int i=1; true; i++) {
					try {
						row.put("col"+i, rs.getObject(i));
					} catch (Exception e) {
						break;
					}
				}
				table.add(row);
			}
			pageData.setList(table);
			String countXsql = Util.reSql(sql);
			stmt = conn.prepareStatement(countXsql);
			addParams(countXsql, stmt, params);
			rs = stmt.executeQuery();
			rs.next();
			int count = rs.getInt(1);
			pageData.setCount(count);
			return pageData;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
		return null;
	}
	
	public static Integer executeUpdate(String sql, List<Object> params) {
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			conn = DB.connect();
			stmt = conn.prepareStatement(sql);
			addParams(sql, stmt, params);
			return new Integer(stmt.executeUpdate());
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
		return null;
	}
	
	private static void addParams(String sql, PreparedStatement stmt, List<Object> params) throws SQLException {
		logger.debug(sql);
		if (params != null) {
			for (int i = 0; i < params.size(); i++) {
				Object o = params.get(i);
				if (o instanceof String) {
					logger.debug("参数"+(i+1)+"字符串:"+params.get(i));
					stmt.setString(i + 1, (String) params.get(i));
				} else if (o instanceof Integer) {
					logger.debug("参数"+(i+1)+"数字型:"+params.get(i));
					stmt.setInt(i + 1, (Integer) params.get(i));
				}
			}
		}
	}
	
}
